<!doctype html>



  


<html class="theme-next mist use-motion" lang="zh-Hans">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>



<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />









  <meta name="baidu-site-verification" content="Q7ugSRSO7V" />







  
  
  <link href="/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />




  
  
  
  

  
    
    
  

  

  

  

  

  
    
    
    <link href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">
  






<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.1.0" rel="stylesheet" type="text/css" />


  <meta name="keywords" content="Hexo, NexT" />








  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.1.0" />






<meta name="description" content="对库（DML）操作添加数据库CREATE DATABASE 库名;CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE DATABASE IF NOT EXISTS 库名; 修改数据库 （修改不了）如果想修改数据库名，那么只能在myisam引擎的基础上，去找mysql/data/该目录记录">
<meta property="og:type" content="article">
<meta property="og:title" content="库表字段CRUD">
<meta property="og:url" content="http://yoursite.com/mysql/库表字段CRUD/index.html">
<meta property="og:site_name" content="chimps">
<meta property="og:description" content="对库（DML）操作添加数据库CREATE DATABASE 库名;CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE DATABASE IF NOT EXISTS 库名; 修改数据库 （修改不了）如果想修改数据库名，那么只能在myisam引擎的基础上，去找mysql/data/该目录记录">
<meta property="og:updated_time" content="2017-04-23T14:14:34.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="库表字段CRUD">
<meta name="twitter:description" content="对库（DML）操作添加数据库CREATE DATABASE 库名;CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE DATABASE IF NOT EXISTS 库名; 修改数据库 （修改不了）如果想修改数据库名，那么只能在myisam引擎的基础上，去找mysql/data/该目录记录">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Mist',
    sidebar: {"position":"left","display":"hide","offset":12,"offset_float":0,"b2t":false,"scrollpercent":false},
    fancybox: true,
    motion: true,
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":30},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="http://yoursite.com/mysql/库表字段CRUD/"/>





  <title> 库表字段CRUD | chimps </title>
</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  





  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?dcb994042608bdb310eff950ba755ff1";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>










  
  
    
  

  <div class="container sidebar-position-left page-post-detail ">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/"  class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">chimps</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle"></p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/mysql/库表字段CRUD/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="chimps">
      <meta itemprop="description" content="">
      <meta itemprop="image" content="https://sfault-avatar.b0.upaiyun.com/416/326/4163260007-58a1621e63a68_huge256">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="chimps">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                库表字段CRUD
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2017-04-23T22:08:05+08:00">
                2017-04-23
              </time>
            

            

            
          </span>

          
            <span class="post-category" >
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/mysql/" itemprop="url" rel="index">
                    <span itemprop="name">mysql</span>
                  </a>
                </span>

                
                
              
            </span>
          

          
            
              <span class="post-comments-count">
                <span class="post-meta-divider">|</span>
                <span class="post-meta-item-icon">
                  <i class="fa fa-comment-o"></i>
                </span>
                <a class="cloud-tie-join-count" href="/mysql/库表字段CRUD/#comments" itemprop="discussionUrl">
                  <span class="post-comments-count join-count" itemprop="commentCount"></span>
                </a>
              </span>
            
          

          
          

          

          

          

        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        <h1 id="对库（DML）操作"><a href="#对库（DML）操作" class="headerlink" title="对库（DML）操作"></a>对库（DML）操作</h1><h2 id="添加数据库"><a href="#添加数据库" class="headerlink" title="添加数据库"></a>添加数据库</h2><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">CREATE</span> <span class="keyword">DATABASE</span> 库名;</div><div class="line"><span class="keyword">CREATE</span> <span class="keyword">DATABASE</span> <span class="string">`test2`</span> <span class="keyword">DEFAULT</span> <span class="built_in">CHARACTER</span> <span class="keyword">SET</span> utf8 <span class="keyword">COLLATE</span> utf8_general_ci;</div><div class="line"><span class="keyword">CREATE</span> <span class="keyword">DATABASE</span> <span class="keyword">IF</span> <span class="keyword">NOT</span> <span class="keyword">EXISTS</span> 库名;</div></pre></td></tr></table></figure>
<h2 id="修改数据库"><a href="#修改数据库" class="headerlink" title="修改数据库"></a>修改数据库</h2><blockquote>
<p>（修改不了）<br>如果想修改数据库名，那么只能在myisam引擎的基础上，去找mysql/data/该目录记录着所有的库。每一个库以文件夹的形式出现。</p>
<h2 id="删除数据库"><a href="#删除数据库" class="headerlink" title="删除数据库"></a>删除数据库</h2><figure class="highlight plain"><table><tr><td class="code"><pre><div class="line">DROP DATABASE 库名</div><div class="line">DROP DATABASE IF EXISTS 库名 	如果存在则删除</div></pre></td></tr></table></figure>
</blockquote>
<h2 id="查询mysql数据库中所有的库"><a href="#查询mysql数据库中所有的库" class="headerlink" title="查询mysql数据库中所有的库"></a>查询mysql数据库中所有的库</h2><p><code>SHOW DATABASES;</code><br>对库中对字符集的操作：</p>
<blockquote>
<p>在创建库的同时设置字符集<br><code>CREATE DATABASE 库名  default charset=utf8;</code><br>修改数据库字符集<br><code>ALTER DATABASE 库名 CHARACTER SET utf8;</code><br>查看数据库字符集<br><code>SHOW CREATE DATABASE 库名;</code><br>删除数据库字符集    <code>（没有删不了）</code><br>选择数据库      <code>USE 数据库名;</code></p>
<h2 id="Mysql中提供的数据类型："><a href="#Mysql中提供的数据类型：" class="headerlink" title="Mysql中提供的数据类型："></a>Mysql中提供的数据类型：</h2><h3 id="基本类型"><a href="#基本类型" class="headerlink" title="基本类型"></a>基本类型</h3><p><code>Tinyint</code>  最小的整型<br><code>Smallint</code>  小的整型<br><code>Mediumint</code> 较小的整型<br><code>Int</code>  标准的整型<br><code>Bigint</code>   大整型<br>注意：在不声明无符号，那么该字段默认情况下是有符号。<br><code>Float</code> 单精度浮点型<br><code>Double(M,D)</code> 双精度浮点型<br>M  表示多少个数<br>D 小数点后面保留多少位<br>     例子：<code>Double(8,2);</code>   <code>123456.78</code><br><code>Decimal(m,d)</code> 字符串类型的浮点型(精确)  金融数据，不是一个数据，属于字符串。<br><code>Char()</code>  定长字符串类型<br><code>Varchar()</code>  变长字符串类型<br><code>Text();</code> 大文本类型</p>
</blockquote>
<h3 id="Char（）和varchar-的区别"><a href="#Char（）和varchar-的区别" class="headerlink" title="Char（）和varchar()的区别"></a>Char（）和varchar()的区别</h3><blockquote>
<p><code>Char()</code>类型限制的字段，如果该字段的内容没有达到括号中的指定长度，那么会用空格占位，占到该长度。浪费空间（硬盘）,查询效率高<br><code>Varchar()</code>类型现在的字段， 如果该字段的内容没有达到括号中的指定长度，那么不会占位，以实际保存的字符串为准，节省了硬盘空间，但是查询时效率低。</p>
</blockquote>
<h1 id="对表的操作"><a href="#对表的操作" class="headerlink" title="对表的操作"></a>对表的操作</h1><h2 id="创建表"><a href="#创建表" class="headerlink" title="创建表"></a>创建表</h2><figure class="highlight"><table><tr><td class="code"><pre><div class="line">CREATE TABLE 表名（</div><div class="line">Id   int   自增  主健索引，</div><div class="line">字段名  字段类型  [列的完整性约束]，</div><div class="line">……</div><div class="line">）engine=myisam default charset=utf8;</div><div class="line"></div><div class="line">`Engine`:指定表引擎</div><div class="line">`Myisam引擎`：快速存储</div><div class="line">`INNODB`  带有事物回滚的引擎。</div><div class="line">注意：创建表的时候，第一个字段，</div><div class="line">必须指定：</div><div class="line">字段名      `*ID`</div><div class="line">字段类型    `int`</div><div class="line">自增        `++`</div><div class="line">设置为主键索引</div><div class="line">列的完整性约束</div><div class="line">自增        `auto_increment`</div><div class="line">主健        `primary key`</div><div class="line">非负数      `unsigned`</div><div class="line">非空        `not null`</div><div class="line">空          `null`</div><div class="line">默认值      `default ‘值’`</div></pre></td></tr></table></figure>
<h2 id="查看当前库中所有的表"><a href="#查看当前库中所有的表" class="headerlink" title="查看当前库中所有的表"></a>查看当前库中所有的表</h2><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">SHOW</span> <span class="keyword">TABLES</span></div></pre></td></tr></table></figure>
<h2 id="查看建表语句："><a href="#查看建表语句：" class="headerlink" title="查看建表语句："></a>查看建表语句：</h2><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">SHOW</span> <span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="keyword">TABLE</span> 表名\G打印完整格式</div></pre></td></tr></table></figure>
<h2 id="查看表结构"><a href="#查看表结构" class="headerlink" title="查看表结构"></a>查看表结构</h2><pre><code>DESC 表名;
</code></pre><h2 id="修改表"><a href="#修改表" class="headerlink" title="修改表"></a>修改表</h2><pre><code>`ALTER TABLE 原表名 RENAME  新表名；`
 备份表  注意 ：（主健索引的约束不能被赋值：------主健    自增属性不能被复制）
`CREATE TABLE 新表名 SELECT * FROM 原表名`
</code></pre><h2 id="删除表"><a href="#删除表" class="headerlink" title="删除表"></a>删除表</h2><pre><code>`DROP TABLE [IF EXISTS] 表名`
[IF EXISTS] 如果存在
[IF NOT EXISTS] 如果不存在
SET FOREIGN_KEY_CHECKS = 1; //设置外键关联
TRUNCATE TABLE file_info; //清空表，并将id置为1
</code></pre><h2 id="查看库中所有表"><a href="#查看库中所有表" class="headerlink" title="查看库中所有表"></a>查看库中所有表</h2><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">select</span> table_name <span class="keyword">from</span> information_schema.tables <span class="keyword">where</span> table_schema=<span class="string">'库名'</span> <span class="keyword">and</span> table_type=<span class="string">'base table'</span>;</div></pre></td></tr></table></figure>
<h2 id="清空表"><a href="#清空表" class="headerlink" title="清空表"></a>清空表</h2><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">TRUNCATE</span> <span class="keyword">TABLE</span> <span class="string">'表名'</span></div></pre></td></tr></table></figure>
<h2 id="外键索引"><a href="#外键索引" class="headerlink" title="外键索引"></a>外键索引</h2><ul>
<li>CASCADE<br>在父表上update/delete记录时，同步update/delete掉子表的匹配记录 </li>
<li>SET NULL<br>在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)  </li>
<li>NO ACTION<br>如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  </li>
<li>RESTRICT<br>同no action, 都是立即检查外键约束</li>
<li><p>SET NULL<br>父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别</p>
</li>
<li><p>NULL、RESTRICT、NO ACTION<br>删除：从表记录不存在时，主表才可以删除。删除从表，主表不变<br>更新：从表记录不存在时，主表才可以更新。更新从表，主表不变</p>
</li>
<li>CASCADE<br>删除：删除主表时自动删除从表。删除从表，主表不变<br>更新：更新主表时自动更新从表。更新从表，主表不变</li>
<li>SET NULL<br>删除：删除主表时自动更新从表值为NULL。删除从表，主表不变<br>更新：更新主表时自动更新从表值为NULL。更新从表，主表不变</li>
</ul>
<h1 id="字段的操作："><a href="#字段的操作：" class="headerlink" title="字段的操作："></a>字段的操作：</h1><h2 id="创建外键约束"><a href="#创建外键约束" class="headerlink" title="创建外键约束"></a>创建外键约束</h2><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> contract <span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> <span class="string">`contract_status_status_id_fk`</span> FOREIGN <span class="keyword">KEY</span> (<span class="string">`status_id`</span>) <span class="keyword">REFERENCES</span> <span class="string">`status`</span> (<span class="string">`status_id`</span>) <span class="keyword">ON</span> <span class="keyword">DELETE</span> <span class="keyword">SET</span> <span class="literal">NULL</span> <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">CASCADE</span></div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> hrcm.permission_init</div><div class="line"><span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> permission_init_user_groups_user_group_id_fk</div><div class="line">FOREIGN <span class="keyword">KEY</span> (user_group_id) <span class="keyword">REFERENCES</span> user_groups (user_group_id) <span class="keyword">ON</span> <span class="keyword">DELETE</span> <span class="keyword">CASCADE</span> <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">CASCADE</span>;</div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> hrcm.permission_init</div><div class="line"><span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> permission_init_types_customer_type_id_fk</div><div class="line">FOREIGN <span class="keyword">KEY</span> (customer_type_id) <span class="keyword">REFERENCES</span> types (type_id) <span class="keyword">ON</span> <span class="keyword">DELETE</span> <span class="keyword">CASCADE</span> <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">CASCADE</span>;</div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> hrcm.permission_init</div><div class="line"><span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> permission_init_types_project_type_id_fk</div><div class="line">FOREIGN <span class="keyword">KEY</span> (project_type_id) <span class="keyword">REFERENCES</span> types (type_id) <span class="keyword">ON</span> <span class="keyword">DELETE</span> <span class="keyword">CASCADE</span> <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">CASCADE</span>;</div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> hrcm.permission_init</div><div class="line"><span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> permission_init_types_contract_type_id_fk</div><div class="line">FOREIGN <span class="keyword">KEY</span> (contract_type_id) <span class="keyword">REFERENCES</span> types (type_id) <span class="keyword">ON</span> <span class="keyword">DELETE</span> <span class="keyword">CASCADE</span> <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">CASCADE</span>;</div></pre></td></tr></table></figure>
<h2 id="增"><a href="#增" class="headerlink" title="增"></a>增</h2><blockquote>
<p><code>ALTER TABLE 表名 ADD 新字段名 字段类型 [列的完整性约束] [位置参数]</code><br>位置参数：<br><code>First</code>   在第一行插入字段<br><code>After</code> 字段名   在该字段名之后插入<br>注意：如果不加位置参数 默认插入到表中最后一行。</p>
</blockquote>
<h2 id="改："><a href="#改：" class="headerlink" title="改："></a>改：</h2><blockquote>
<p>第一种形式：只修改字段的类型<br><code>ALTER TABLE 表名 MODIFY 字段名 字段类型 [列的完整性约束];</code><br>第二种形式：既修改字段名的同时也修改字段类型<br><code>ALTER TABLE 表名 CHANGE 原字段名  新字段名 字段类型 [列的完整性约束]</code><br>注意：MODIFY 只能修改字段类型，CHANGE可以修改字段类型也能修改字段名。</p>
</blockquote>
<h2 id="删"><a href="#删" class="headerlink" title="删"></a>删</h2><blockquote>
<p><code>ALTER TABLE 表名 DROP 字段名；</code> 删除一条<br>注意：如果要删除多条字段<br><code>ALTER TABLE 表名 DROP 字段名1,DROP 字段名2……;</code></p>
<h2 id="查"><a href="#查" class="headerlink" title="查"></a>查</h2><p>DESC 表名</p>
</blockquote>
<h2 id="Mysql-数据库中的运算符"><a href="#Mysql-数据库中的运算符" class="headerlink" title="Mysql 数据库中的运算符"></a>Mysql 数据库中的运算符</h2><p>注意：数据库帮我们存储内容已经很忙了，至于运算就不要麻烦人家了。<br><code>+ - * /</code><br><code>MOD   %：</code>取余<br><code>DIV</code>:取整<br>列：<code>SELECT 值1 运算符 值2；</code></p>
<h2 id="比较运算符"><a href="#比较运算符" class="headerlink" title="比较运算符"></a>比较运算符</h2><figure class="highlight"><table><tr><td class="code"><pre><div class="line">=</div><div class="line">！= 或者 &lt;&gt;  不等于</div><div class="line">&lt;=&gt; 安全等于</div><div class="line">&lt;</div><div class="line">&gt;</div><div class="line">&lt;=</div><div class="line">&gt;=</div><div class="line"></div><div class="line">Between :存在于指定的范围</div><div class="line">In  :存在于指定的集合</div><div class="line">Is null :为null</div><div class="line">Is not null :不为null</div><div class="line">Like 通配符匹配</div><div class="line">Regexp或 rlik 正则匹配</div><div class="line">逻辑运算符 （推荐使用英文运算符）</div><div class="line">Not 或 ！ 逻辑非</div><div class="line">And 或者 &amp;&amp; 逻辑与</div><div class="line">Or 或者 ||  逻辑或</div><div class="line">XOR  逻辑异或</div><div class="line">位运算符</div><div class="line">&amp; 按位与</div><div class="line">|  按位或</div><div class="line">~  按位取反</div><div class="line">&lt;&lt; 左移</div><div class="line">&gt;&gt; 右移</div><div class="line">函数：</div><div class="line">Md5():加密</div><div class="line">COUNT();统计长度</div><div class="line">Database();查看当前库函数</div><div class="line">Sum(); 聚合函数</div></pre></td></tr></table></figure>
<hr>
<h1 id="DML语句的操作"><a href="#DML语句的操作" class="headerlink" title="DML语句的操作"></a>DML语句的操作</h1><p>内容：<br>User:<br><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line">Id     int         auto_increment primary key,</div><div class="line">Name    varchar(30) not             null,</div><div class="line">Sex     tinyint     unsigned        not null              default 0,   //  0=女 1=男</div><div class="line">Age     tinyint     unsigned        not null</div><div class="line">)engine=myisam default charset=utf8;</div></pre></td></tr></table></figure></p>
<p>分析desc 表名 返回的结构<br>| Field   字段名<br>| Type   字段类型<br>| Null    是否为空    YES空   no 不为空<br>| Key     主健<br>| Default  默认值<br>| Extra    额外的     auto_increment 自增。</p>
<h2 id="查看数据的第一种格式："><a href="#查看数据的第一种格式：" class="headerlink" title="查看数据的第一种格式："></a>查看数据的第一种格式：</h2><p>SELECT <em> FROM 表名
</em>表示匹配所有的字段。</p>
<h2 id="添加数据"><a href="#添加数据" class="headerlink" title="添加数据"></a>添加数据</h2><h3 id="方式1："><a href="#方式1：" class="headerlink" title="方式1："></a>方式1：</h3><blockquote>
<p><code>INSERT INTO 表名(字段名1，字段名2……) values(值1，值2，……)；</code><br>注意：如果字段名两边要加引号，那么加反引号。<br>如果在值中加入引号，加单引号或双引号。<br>对于值什么时候加引号，什么时候不加引号：<br>可以不加引号的类型为：整型  浮点型里面的float<br>字段名可以跟数据表中的顺序不对应。但是值与字段名必须要一一对应。<br>在该方式下添加数据，可以忽略掉某个字段的值，</p>
<h3 id="方式2"><a href="#方式2" class="headerlink" title="方式2"></a>方式2</h3><p><code>INSERT INTO 表名 VALUES(‘值1’，值2，……);</code><br>注意：所有的值必须按照表中字段的顺序 一一对应赋值，不写值的可以写null，但是不能省略。</p>
<h3 id="方法3-（批量操作）"><a href="#方法3-（批量操作）" class="headerlink" title="方法3 :（批量操作）"></a>方法3 :（批量操作）</h3><p><code>INSERT INTO 表名(字段名1，字段名2，……) values(值1，值2……),(值1，值2……)，……；</code></p>
<h3 id="方式4：-批量添加"><a href="#方式4：-批量添加" class="headerlink" title="方式4：(批量添加)"></a>方式4：(批量添加)</h3><p><code>INSERT INTO 表名 VALUES(‘值1’，值2，……),(‘值1’，值2，……),……;</code></p>
<h2 id="修改数据"><a href="#修改数据" class="headerlink" title="修改数据"></a>修改数据</h2><p><code>UPDATE 表名 set 字段名=新值，字段名=新值，……[更新条件];</code><br>更改条件：<br><code>Where 字段 is null （字段为空）
Where  字段  is not null （字段不为空）
Where   id=值（条件指定ID=某个值）</code><br>SQL语句的特点：相同的地方用,隔开，不同的地方用空格隔开。！</p>
<h2 id="删除数据"><a href="#删除数据" class="headerlink" title="删除数据"></a>删除数据</h2><p><code>DELETE FROM 表名 [删除条件]</code><br>注意：在删除某个数据的时候，各位亲一定要记住必须个给删除条件。<br>如果不给删除条件的后果-&gt;没有数据<br>删除条件：<br><code>Where  id=值；
Where  id&lt;A值 or id&gt;B值</code><br>删除范围：<code>between</code><br><code>Where  字段 between 值1 and 值2（在什么什么之间）
在指定的范围内 包含值1和值2；</code><br>删除指定的集合:<code>in</code><br><code>Where 字段 in(集合：都需要的值)；</code></p>
</blockquote>
<hr>
<h1 id="DQL："><a href="#DQL：" class="headerlink" title="DQL："></a>DQL：</h1><p>SELECT<br>查询表中所有的数据<br>SELECT * FROM 表名<br>方式2<br>SELECT 字段1，字段2…… From 表名 [查询条件]<br>SELECT [[库.]表.]字段名  from  [库.]表  [查询条件]<br>查询哪一个库下的哪一个表下的字段，在哪里去查    </p>
<h2 id="查询条件"><a href="#查询条件" class="headerlink" title="查询条件"></a>查询条件</h2><h3 id="指定的范围"><a href="#指定的范围" class="headerlink" title="指定的范围"></a>指定的范围</h3><p><code>Between</code> 值1 and 值2<br><code>In（</code>）指定的集合<br><code>In(值1，值2，值3)</code><br><code>NOT IN(值1，值2)</code> 不在这个指定集合内的<br> <code>SELECT * FROM admin WHERE id NOT in(2,3,4,5,8,10);</code></p>
<h3 id="LIKE模式匹配"><a href="#LIKE模式匹配" class="headerlink" title="LIKE模式匹配"></a><code>LIKE</code>模式匹配</h3><p><code>WHERE 字段名 like ‘%值%’</code><br>只要该字段中出现过这个值就能查询到<br><code>select * from admin where name like &#39;%冉%&#39;;
WHERE 字段名 like ‘值%’;</code><br>只要改字段中第一个位置为指定的这个值就能查询到<br><code>select * from admin where name like &#39;冉%&#39;;</code><br>WHERE 字段名 like ‘%值’；<br>只要该字段中最后一个位置是指定的这个值，那么能查询到<br> <code>select * from admin where name like &#39;%冉&#39;;
WHERE 字段名 LIKE ‘_值%’;</code><br>表示该字段名中第二个位置出现的字符是指定的值，就能查询到<br><code>select * from admin where name like &#39;_冉%&#39;;</code><br><code>_</code>表示一个位置的字符<br><code>%</code> 表示任意内容可以出现0次一次或者多次。</p>
<h3 id="查询表中到底有多少条数据"><a href="#查询表中到底有多少条数据" class="headerlink" title="查询表中到底有多少条数据"></a>查询表中到底有多少条数据</h3><p><code>COUNT(*)或者 count(’字段名‘)</code><br>例：Select  count(id) FROM admin;</p>
<h2 id="限制查询数量"><a href="#限制查询数量" class="headerlink" title="限制查询数量"></a>限制查询数量</h2><p><code>SELECT 字段 FROM 表名 [查询条件] LIMIT 查询数量</code><br>例：<code>SELECT * FROM admin WHERE sex=1 LIMIT 4;</code><br><code>SELECT 字段 FROM 表名 [查询条件] LIMIT</code> 跳过几条，查询几条。<br>例：<code>SELECT * FROM admin WHERE sex=1 LIMIT 6,2;</code></p>
<h2 id="排序查询"><a href="#排序查询" class="headerlink" title="排序查询"></a>排序查询</h2><p>两类：一个是正序   一个是倒序<br><code>ORDER BY 字段名 ASC  正序 （默认）
ORDER BY 字段名 DESC 倒序
SELECT * FROM admin ORDER BY id DESC;</code><br>按照age正序 如果age相同按照sex倒序 如果sex相同按照ID正序；<br><code>SELECT * FROM admin ORDER BY age asc,sex desc,id asc;</code></p>
<h2 id="分组-GROUP-BY"><a href="#分组-GROUP-BY" class="headerlink" title="分组 GROUP BY"></a>分组 GROUP BY</h2><blockquote>
<p><code>GROUP BY 字段名</code><br>查询每个部门的总工资<br><code>SELECT bumen,sum(gongzi) from gz GROUP BY bumen;</code><br>查询每个部门有多少人<br><code>SELECT bumen,count(gongzi) from gz GROUP BY bumen;</code><br>查询部门编号大于2 并且部门总工作小于1000；<br><code>SELECT bumen,sum(gongzi) from gz WHERE bumen&gt;2 GROUP BY bumen HAVING sum(gongzi) &lt; 1000;</code><br>示例：<br><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">SELECT</span></div><div class="line">  <span class="keyword">count</span>(title) <span class="keyword">AS</span> c,</div><div class="line">  author</div><div class="line"><span class="keyword">FROM</span></div><div class="line">  (<span class="keyword">SELECT</span></div><div class="line">     from_unixtime(cv.timestamp, <span class="string">'%Y%m%d'</span>) <span class="keyword">AS</span> d,</div><div class="line">     cv.title,</div><div class="line">     cv.username</div><div class="line">   <span class="keyword">FROM</span> content_versions <span class="keyword">AS</span> cv <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> <span class="keyword">content</span> <span class="keyword">AS</span> co <span class="keyword">ON</span> co.coid = cv.coid</div><div class="line">     <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> <span class="keyword">users</span> <span class="keyword">AS</span> u <span class="keyword">ON</span> cv.username = u.username</div><div class="line">   <span class="keyword">WHERE</span> co.format = <span class="string">'smart'</span> <span class="keyword">AND</span> u.type = <span class="string">'company'</span> <span class="keyword">AND</span> cv.author <span class="keyword">NOT</span> <span class="keyword">LIKE</span> <span class="string">'%律师'</span></div><div class="line">   <span class="keyword">GROUP</span> <span class="keyword">BY</span> username, d, title</div><div class="line">   <span class="keyword">ORDER</span> <span class="keyword">BY</span> d, username) res</div><div class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> username</div><div class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> c <span class="keyword">DESC</span> <span class="keyword">LIMIT</span> <span class="number">0</span>,<span class="number">20</span>;</div></pre></td></tr></table></figure></p>
</blockquote>
<p><strong>一般使用在分组里面做二次过滤</strong><br>使用方式和where一样</p>
<h2 id="流程控制-CASE"><a href="#流程控制-CASE" class="headerlink" title="流程控制(CASE)"></a>流程控制(CASE)</h2><h3 id="CASE-Syntax"><a href="#CASE-Syntax" class="headerlink" title="CASE Syntax"></a>CASE Syntax</h3><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line">CASE case_value</div><div class="line">    WHEN when_value THEN statement_list</div><div class="line">    [WHEN when_value THEN statement_list] ...</div><div class="line">    [ELSE statement_list]</div><div class="line"><span class="keyword">END</span> <span class="keyword">CASE</span></div><div class="line"></div><div class="line"><span class="keyword">Or</span>:</div><div class="line"></div><div class="line"><span class="keyword">CASE</span></div><div class="line">    <span class="keyword">WHEN</span> search_condition <span class="keyword">THEN</span> statement_list</div><div class="line">    [<span class="keyword">WHEN</span> search_condition <span class="keyword">THEN</span> statement_list] ...</div><div class="line">    [<span class="keyword">ELSE</span> statement_list]</div><div class="line"><span class="keyword">END</span> <span class="keyword">CASE</span></div></pre></td></tr></table></figure>
<h3 id="Example"><a href="#Example" class="headerlink" title="Example"></a>Example</h3><figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">SELECT</span></div><div class="line">  <span class="keyword">oid</span>,</div><div class="line">  <span class="keyword">type</span>,</div><div class="line">  <span class="keyword">status</span>,</div><div class="line">  create_time</div><div class="line"><span class="keyword">FROM</span> orders</div><div class="line"><span class="keyword">WHERE</span> <span class="keyword">type</span> <span class="keyword">IN</span></div><div class="line">      (<span class="string">'default'</span>, <span class="string">'package_stock_based_compensation'</span>, <span class="string">'package_financing'</span>, <span class="string">'consult'</span>, <span class="string">'package_law_serve'</span>, <span class="string">'package_neeg'</span>)</div><div class="line"></div><div class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="keyword">field</span>(<span class="keyword">status</span>,</div><div class="line">               <span class="string">'lawyer_finished'</span>,</div><div class="line">               <span class="keyword">CASE</span> <span class="keyword">WHEN</span> <span class="keyword">type</span> <span class="keyword">IN</span></div><div class="line">                         (<span class="string">'default'</span>, <span class="string">'package_stock_based_compensation'</span>, <span class="string">'package_financing'</span>, <span class="string">'package_law_serve'</span>)</div><div class="line">                 <span class="keyword">THEN</span> <span class="string">'paid'</span></div><div class="line">               <span class="keyword">WHEN</span> <span class="keyword">type</span> <span class="keyword">IN</span> (<span class="string">'consult'</span>, <span class="string">'package_neeg'</span>)</div><div class="line">                 <span class="keyword">THEN</span> <span class="string">'confirmed'</span> <span class="keyword">END</span></div><div class="line">) <span class="keyword">DESC</span>;</div></pre></td></tr></table></figure>
<h2 id="字符串处理"><a href="#字符串处理" class="headerlink" title="字符串处理"></a>字符串处理</h2><blockquote>
<ul>
<li><code>SUBSTRING（string，start，length）</code></li>
<li><code>IF(条件，满足条件的返回的值，不满足条件的返回的值)</code></li>
<li><code>WHERE name LIKE CONCAT(&#39;%&#39;,:n,&#39;%&#39;)</code><br>示例：<figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">SELECT</span></div><div class="line">  <span class="keyword">count</span>(ip) <span class="keyword">AS</span> c,</div><div class="line">  <span class="keyword">domain</span></div><div class="line"><span class="keyword">FROM</span> (<span class="keyword">SELECT</span></div><div class="line">        <span class="keyword">count</span>(*)                                                                                           <span class="keyword">AS</span> c,</div><div class="line">        ip,</div><div class="line">        <span class="keyword">IF</span>(<span class="keyword">LOCATE</span>(<span class="string">'https://'</span>, refer), </div><div class="line">            <span class="keyword">SUBSTRING</span>(refer, <span class="number">9</span>, <span class="keyword">LOCATE</span>(<span class="string">'/'</span>, refer, <span class="number">9</span>) - <span class="number">9</span>), </div><div class="line">            <span class="keyword">if</span>(<span class="keyword">LOCATE</span>(<span class="string">'http://'</span>, refer),</div><div class="line">                <span class="keyword">SUBSTRING</span>(refer, <span class="number">8</span>, <span class="keyword">if</span>( (<span class="keyword">LOCATE</span>(<span class="string">'/'</span>, refer, <span class="number">8</span>) - <span class="number">8</span>) &gt; <span class="number">0</span>,</div><div class="line">                <span class="keyword">LOCATE</span>(<span class="string">'/'</span>, refer, <span class="number">8</span>) - <span class="number">8</span>, <span class="number">30</span>)), </div><div class="line">            refer)</div><div class="line">        ) <span class="keyword">AS</span> <span class="keyword">domain</span></div><div class="line">      <span class="keyword">FROM</span> access_log</div><div class="line">      <span class="keyword">WHERE</span> <span class="keyword">path</span> = <span class="string">'/'</span> <span class="keyword">AND</span> <span class="keyword">time</span> &gt; <span class="number">1470528000</span></div><div class="line">      <span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="keyword">domain</span>, ip</div><div class="line">      <span class="keyword">ORDER</span> <span class="keyword">BY</span> c <span class="keyword">DESC</span>) <span class="keyword">AS</span> res</div><div class="line"><span class="keyword">WHERE</span> <span class="keyword">domain</span> <span class="keyword">NOT</span> <span class="keyword">LIKE</span> <span class="string">'%hetongjia%'</span></div><div class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="keyword">domain</span></div><div class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> c <span class="keyword">DESC</span></div></pre></td></tr></table></figure>
</li>
</ul>
</blockquote>
<h2 id="多表联合查询-where-形式"><a href="#多表联合查询-where-形式" class="headerlink" title="多表联合查询  (where 形式)"></a>多表联合查询  (where 形式)</h2><p>SELECT 表1.字段，表2.字段 from 表1，表2 where 表1.字段=表2.字段<br>注意: 表1里面有一个字段和表2里面的一个字段关联<br><figure class="highlight"><table><tr><td class="code"><pre><div class="line"># 例子：</div><div class="line">SELECT gz.id,gz.name,gz.gongzi,bm.name FROM gz,bm WHERE gz.bumen=bm.id ORDER BY gz.id asc;</div><div class="line"># 左关联</div><div class="line">SELECT 表1,字段 表2.字段 from 表1  LEFT JOIN 表2 ON 表1.字段=表2.字段 </div><div class="line">SELECT gz.id,gz.name,gz.gongzi,bm.name FROM gz left join bm ON gz.bumen=bm.id;</div><div class="line"># 右关联</div><div class="line">SELECT 表1,字段 表2.字段 from 表1  right JOIN 表2 ON 表1.字段=表2.字段 </div><div class="line">mysql&gt; SELECT gz.id,gz.name,gz.gongzi,bm.name FROM gz right join bm ON gz.bumen=bm.id;</div><div class="line"># As的不讲了PHP中讲</div></pre></td></tr></table></figure></p>
<h2 id="去重"><a href="#去重" class="headerlink" title="去重"></a>去重</h2><ul>
<li><p>distinct(只能查一个字段)</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><div class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> <span class="keyword">name</span> <span class="keyword">from</span> <span class="keyword">table</span></div></pre></td></tr></table></figure>
</li>
<li><p>group by</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><div class="line">// 对name去重，并且能查到其他字段</div><div class="line"><span class="keyword">select</span> <span class="keyword">id</span>, <span class="keyword">name</span> <span class="keyword">from</span> <span class="keyword">table</span> <span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">name</span></div></pre></td></tr></table></figure>
</li>
</ul>
<h1 id="DCL-语句"><a href="#DCL-语句" class="headerlink" title="DCL 语句"></a>DCL 语句</h1><p> 添加用户 控制权限<br>GRANT 权限（增 删  改  查） ON 库.表 TO 新用户名@localhost IDENTIFIED BY ‘密码’<br>鉴定（identify的过去分词）  grant: 授予<br>GRANT SELECT,INSERT ON lamp115.gz TO ranran@localhost  IDENTIFIED BY ‘daxiong’;<br> FLUSH PRIVILEGES;  刷新</p>
<h1 id="数据库的导入和导出"><a href="#数据库的导入和导出" class="headerlink" title="数据库的导入和导出"></a>数据库的导入和导出</h1><h2 id="数据库的导出"><a href="#数据库的导出" class="headerlink" title="数据库的导出"></a>数据库的导出</h2><p><code>mysqldump [-h localhost] -u 用户名 -p 密码 库名 [表名] &gt; 导出的文件名</code><br>例子：<br><code>mysqldump -u root -p lamp115 &gt; wamp/www/a.sql</code><br>只导出表结构：<br><code>mysqldump -u root -d -p lamp115 &gt; wamp/www/a.sql</code></p>
<blockquote>
<p>导出的文件地址 默认取决于cmd命令行里：C:><br>导出的文件名前可以加地址</p>
</blockquote>
<h2 id="数据库的导入"><a href="#数据库的导入" class="headerlink" title="数据库的导入"></a>数据库的导入</h2><p><code>Mysql [-h localhost] -u 用户名 -p 密码</code> 要导入的库名 &lt; 导入文件的路径<br>例：<code>mysql -u root -p aabb &lt; wamp/www/a.sql</code><br>注意：</p>
<blockquote>
<p>导出库不是将真正的库导出，是把库中所有的表导出<br>所以在导入的时候必须指定一个存在的库名。</p>
</blockquote>

      
    </div>

    <div>
      
        

      
    </div>

    <div>
      
        

      
    </div>

    <div>
      
        

      
    </div>

    <footer class="post-footer">
      

      
        
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/nginx/rewrite规则/" rel="next" title="rewrite 规则">
                <i class="fa fa-chevron-left"></i> rewrite 规则
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/mysql/函数/" rel="prev" title="函数">
                函数 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          
  <div class="comments" id="comments">
    
      <div id="cloud-tie-wrapper" class="cloud-tie-wrapper"></div>
    
  </div>


        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap" >
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
          <img class="site-author-image" itemprop="image"
               src="https://sfault-avatar.b0.upaiyun.com/416/326/4163260007-58a1621e63a68_huge256"
               alt="chimps" />
          <p class="site-author-name" itemprop="name">chimps</p>
           
              <p class="site-description motion-element" itemprop="description"></p>
          
        </div>
        <nav class="site-state motion-element">

          
            <div class="site-state-item site-state-posts">
              <a href="/archives">
                <span class="site-state-item-count">59</span>
                <span class="site-state-item-name">日志</span>
              </a>
            </div>
          

          
            
            
            <div class="site-state-item site-state-categories">
              <a href="/categories/index.html">
                <span class="site-state-item-count">12</span>
                <span class="site-state-item-name">分类</span>
              </a>
            </div>
          

          
            
            
            <div class="site-state-item site-state-tags">
              <a href="/tags/index.html">
                <span class="site-state-item-count">19</span>
                <span class="site-state-item-name">标签</span>
              </a>
            </div>
          

        </nav>

        

        <div class="links-of-author motion-element">
          
        </div>

        
        

        
        

        


      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#对库（DML）操作"><span class="nav-number">1.</span> <span class="nav-text">对库（DML）操作</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#添加数据库"><span class="nav-number">1.1.</span> <span class="nav-text">添加数据库</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#修改数据库"><span class="nav-number">1.2.</span> <span class="nav-text">修改数据库</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#删除数据库"><span class="nav-number">1.3.</span> <span class="nav-text">删除数据库</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#查询mysql数据库中所有的库"><span class="nav-number">1.4.</span> <span class="nav-text">查询mysql数据库中所有的库</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql中提供的数据类型："><span class="nav-number">1.5.</span> <span class="nav-text">Mysql中提供的数据类型：</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#基本类型"><span class="nav-number">1.5.1.</span> <span class="nav-text">基本类型</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Char（）和varchar-的区别"><span class="nav-number">1.5.2.</span> <span class="nav-text">Char（）和varchar()的区别</span></a></li></ol></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#对表的操作"><span class="nav-number">2.</span> <span class="nav-text">对表的操作</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#创建表"><span class="nav-number">2.1.</span> <span class="nav-text">创建表</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#查看当前库中所有的表"><span class="nav-number">2.2.</span> <span class="nav-text">查看当前库中所有的表</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#查看建表语句："><span class="nav-number">2.3.</span> <span class="nav-text">查看建表语句：</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#查看表结构"><span class="nav-number">2.4.</span> <span class="nav-text">查看表结构</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#修改表"><span class="nav-number">2.5.</span> <span class="nav-text">修改表</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#删除表"><span class="nav-number">2.6.</span> <span class="nav-text">删除表</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#查看库中所有表"><span class="nav-number">2.7.</span> <span class="nav-text">查看库中所有表</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#清空表"><span class="nav-number">2.8.</span> <span class="nav-text">清空表</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#外键索引"><span class="nav-number">2.9.</span> <span class="nav-text">外键索引</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#字段的操作："><span class="nav-number">3.</span> <span class="nav-text">字段的操作：</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#创建外键约束"><span class="nav-number">3.1.</span> <span class="nav-text">创建外键约束</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#增"><span class="nav-number">3.2.</span> <span class="nav-text">增</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#改："><span class="nav-number">3.3.</span> <span class="nav-text">改：</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#删"><span class="nav-number">3.4.</span> <span class="nav-text">删</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#查"><span class="nav-number">3.5.</span> <span class="nav-text">查</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql-数据库中的运算符"><span class="nav-number">3.6.</span> <span class="nav-text">Mysql 数据库中的运算符</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#比较运算符"><span class="nav-number">3.7.</span> <span class="nav-text">比较运算符</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#DML语句的操作"><span class="nav-number">4.</span> <span class="nav-text">DML语句的操作</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#查看数据的第一种格式："><span class="nav-number">4.1.</span> <span class="nav-text">查看数据的第一种格式：</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#添加数据"><span class="nav-number">4.2.</span> <span class="nav-text">添加数据</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#方式1："><span class="nav-number">4.2.1.</span> <span class="nav-text">方式1：</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#方式2"><span class="nav-number">4.2.2.</span> <span class="nav-text">方式2</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#方法3-（批量操作）"><span class="nav-number">4.2.3.</span> <span class="nav-text">方法3 :（批量操作）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#方式4：-批量添加"><span class="nav-number">4.2.4.</span> <span class="nav-text">方式4：(批量添加)</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#修改数据"><span class="nav-number">4.3.</span> <span class="nav-text">修改数据</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#删除数据"><span class="nav-number">4.4.</span> <span class="nav-text">删除数据</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#DQL："><span class="nav-number">5.</span> <span class="nav-text">DQL：</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#查询条件"><span class="nav-number">5.1.</span> <span class="nav-text">查询条件</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#指定的范围"><span class="nav-number">5.1.1.</span> <span class="nav-text">指定的范围</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#LIKE模式匹配"><span class="nav-number">5.1.2.</span> <span class="nav-text">LIKE模式匹配</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查询表中到底有多少条数据"><span class="nav-number">5.1.3.</span> <span class="nav-text">查询表中到底有多少条数据</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#限制查询数量"><span class="nav-number">5.2.</span> <span class="nav-text">限制查询数量</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#排序查询"><span class="nav-number">5.3.</span> <span class="nav-text">排序查询</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#分组-GROUP-BY"><span class="nav-number">5.4.</span> <span class="nav-text">分组 GROUP BY</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#流程控制-CASE"><span class="nav-number">5.5.</span> <span class="nav-text">流程控制(CASE)</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#CASE-Syntax"><span class="nav-number">5.5.1.</span> <span class="nav-text">CASE Syntax</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Example"><span class="nav-number">5.5.2.</span> <span class="nav-text">Example</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#字符串处理"><span class="nav-number">5.6.</span> <span class="nav-text">字符串处理</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#多表联合查询-where-形式"><span class="nav-number">5.7.</span> <span class="nav-text">多表联合查询  (where 形式)</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#去重"><span class="nav-number">5.8.</span> <span class="nav-text">去重</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#DCL-语句"><span class="nav-number">6.</span> <span class="nav-text">DCL 语句</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#数据库的导入和导出"><span class="nav-number">7.</span> <span class="nav-text">数据库的导入和导出</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#数据库的导出"><span class="nav-number">7.1.</span> <span class="nav-text">数据库的导出</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#数据库的导入"><span class="nav-number">7.2.</span> <span class="nav-text">数据库的导入</span></a></li></ol></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright" >
  
  &copy; 
  <span itemprop="copyrightYear">2017</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">chimps</span>
</div>


<div class="powered-by">
  由 <a class="theme-link" href="https://hexo.io">Hexo</a> 强力驱动
</div>

<div class="theme-info">
  主题 -
  <a class="theme-link" href="https://github.com/iissnan/hexo-theme-next">
    NexT.Mist
  </a>
</div>


        

        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  








  
  <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>

  
  <script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>

  
  <script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>

  
  <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.1.0"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.1.0"></script>



  
  

  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.0"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.1.0"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.0"></script>



  


  




	





  
    
    <script>
      var cloudTieConfig = {
        url: document.location.href, 
        sourceId: "",
        productKey: "48bb30d8783d4b38ba0c95e0283238ca",
        target: "cloud-tie-wrapper"
      };
    </script>
    <script src="https://img1.ws.126.net/f2e/tie/yun/sdk/loader.js"></script>
  










  





  

  
<script>
(function(){
    var bp = document.createElement('script');
    var curProtocol = window.location.protocol.split(':')[0];
    if (curProtocol === 'https') {
        bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';        
    }
    else {
        bp.src = 'http://push.zhanzhang.baidu.com/push.js';
    }
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(bp, s);
})();
</script>


  

  

  

</body>
</html>
